﻿
/****** Object:  User Defined Function dbo.CleanupPhone    Script Date: 10/14/2009 10:39:06 PM ******/

CREATE FUNCTION CleanupPhone ( @PhoneNumber varchar(20) )  
RETURNS varchar(20) AS  
BEGIN 
	declare @strRet varchar(20)
	declare @i int
	
	set @strRet = @PhoneNumber
	set @i = 1
	while @i <= len(@strRet)
	begin
		if substring( @strRet, @i, 1) between '0' and '9'
			set @i = @i + 1
		else
			if @i > 1
				if @i < len( @strRet )
					set @strRet = substring( @strRet, 1, @i - 1 ) + substring( @strRet, @i + 1, len(@strRet) - @i )
				else
					set @strRet = substring( @strRet, 1, @i - 1 )
			else 
				if @i < len( @strRet )
					set @strRet = substring( @strRet, 2, len( @strRet ) - 1 )
				else
					set @strRet = ''
	end

	return @strRet
END



